﻿using LinqOverView.Models;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace LinqOverView
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            AllDemoDBContext alldemoDBCOntext = new AllDemoDBContext();
            textBox1.Text = alldemoDBCOntext.Customers.Where(d => d.ID == 1).Select(d => d.Name).First<string>();
            //var selectvalue = from customer in alldemoDBCOntext.Customers
            //                 where customer.ID == 1
            //                 select customer.Name;
            // textBox1.Text = selectvalue.First<string>();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            dataGridView1.DataSource = null;
            AllDemoDBContext alldemoDBCOntext = new AllDemoDBContext();
            var datasource = alldemoDBCOntext.Customers.Where(d => d.ID > 0);
            var value2 = datasource.AsEnumerable().ToList();
            BindingSource bidingSource = new BindingSource();
            bidingSource.DataSource = value2;
            dataGridView1.DataSource = bidingSource;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            dataGridView1.DataSource = null;
            AllDemoDBContext alldemoDBCOntext = new AllDemoDBContext();
            var datasource = alldemoDBCOntext.Customers.Select(d => new { d.Name, d.ID });
            var value2 = datasource.AsEnumerable().ToList();
            BindingSource bidingSource = new BindingSource();
            bidingSource.DataSource = value2;
            dataGridView1.DataSource = bidingSource;
        }
        /// <summary>
        /// 子查询
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button4_Click(object sender, EventArgs e)
        {
            AllDemoDBContext alldemoDBCOntext = new AllDemoDBContext();
            //var query = from q in alldemoDBCOntext.Customers
            //            select new
            //            {
            //                q.Name,
            //                Purchases = from w
            //                            in alldemoDBCOntext.Purchases
            //                            where w.CustomerID == q.ID
            //                            select new { w.Description, w.Price }
            //            };
            var query=  from c in alldemoDBCOntext.Customers
            select new
            {
                c.Name,
                Purchases = from p in c.Purchases // Purchases is EntitySet<Purchase>
                            where p.Price > 10
                            select new { p.Description, p.Price }
            };

            //var queryLambda = alldemoDBCOntext.Customers.Join(alldemoDBCOntext.Purchases, c => c.ID, d => d.CustomerID, (c, d) => new
            //{
            //    Name = c.Name,
            //    Description = d.Description,
            //    Price = d.Price
            //}).DefaultIfEmpty();

            //var queryList = queryLambda.AsEnumerable().ToList();

            List<CustPurchase> custPurchase = new List<CustPurchase>();
            foreach (var x in query)
            {
                foreach(var y in x.Purchases)
                {
                    CustPurchase custPurcha = new CustPurchase
                    {
                        CustName = x.Name,
                        Description = y.Description,
                        Price = y.Price
                    };
                    custPurchase.Add(custPurcha);

                }
           
            }
        
            BindingSource bidingSource = new BindingSource();
            bidingSource.DataSource = custPurchase;
            dataGridView1.DataSource = bidingSource;
        }
    }

    internal class CustPurchase
    {
        public string CustName { get; set; }
        public string Description { get; set; }

        public decimal Price { get; set; }
    }
}
